﻿using OfficeOpenXml;
using System.IO;
using System;
using System.Data;
using System.Collections.Generic;

namespace ExcelHelper
{
    public class ExcelHandler
    {
        #region 数据导出
        /// <summary>
        /// 数据导出
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="DicHeaders"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string Export(string FileName, Dictionary<string, string> DicHeaders, DataTable dt)
        {
            string sWebRootFolder = Directory.GetCurrentDirectory() + "\\wwwroot\\Data";
            //string URL = string.Format("{0}://{1}/{2}", Request.Scheme, Request.Host, sFileName);
            string filePath = Path.Combine(sWebRootFolder, FileName);
            FileInfo file = new FileInfo(filePath);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(filePath);
            }
            using (ExcelPackage package = new ExcelPackage(file))
            {
                // add a new worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
                int counts = DicHeaders.Count;
                int i = 1;
                foreach (KeyValuePair<string, string> item in DicHeaders)
                {
                    worksheet.Cells[1, i++].Value = item.Key;
                }
                for (int x = 0; x < dt.Rows.Count; x++)
                {
                    for (int y = 0; y < dt.Columns.Count; y++)
                    {
                        worksheet.Cells[x + 1, y + 1].Value = dt.Rows[x][y];
                    }
                }
                package.Save(); //Save the workbook.
            }
            return FileName;

        }
        #endregion

        #region 废弃
        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        //public List<LoginModel> GetSheetValues(string filepath)
        //{
        //    FileInfo file = new FileInfo(filepath);
        //    if (file != null)
        //    {
        //        using(ExcelPackage package = new ExcelPackage(file))
        //        {
        //            ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
        //            int rowCount = worksheet.Dimension.Rows;
        //            int colCount = worksheet.Dimension.Columns;
        //            List< LoginModel> models = new List<LoginModel>();
        //            for (int row = 1; row <= rowCount; row++)
        //            {
        //                LoginModel model = new LoginModel();
        //                model.UserName = worksheet.Cells[row, 1].Value.ToString();
        //                model.Password = worksheet.Cells[row, 2].Value.ToString();
        //                models.Add(model);
        //            }
        //            return models;
        //        }
        //    }
        //    return null;
        //}
        #endregion

        #region GetString
        private static string GetString(object obj)
        {
            try
            {
                return obj.ToString();
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        #endregion

        #region 将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// </summary>
        /// <param name="filePath">文件的绝对路径</param>
        /// <returns></returns>
        public DataTable WorksheetToTable(string filePath)
        {
            try
            {
                FileInfo existingFile = new FileInfo(filePath);

                ExcelPackage package = new ExcelPackage(existingFile);
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//选定 指定页

                return WorksheetToTable(worksheet);
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region 将worksheet转成datatable
        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    try
                    {
                        //默认将第一行设置为datatable的标题
                        if (i == 1)
                            dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                        //处理标题行字段为空
                        //剩下的写入datatable
                        else
                            dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                    }
                    catch
                    {
                        continue;
                    }
                }
            }
            return dt;
        }
        #endregion

    }
}
